﻿using CommonTools;
using shop.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace shop.Bll.Logi
{ 
    /// <summary> 物流账单 </summary>
    public class LogisticsBillBll
    {
        public async Task<Result> SaveItems(IEnumerable<LogisticsBillItem> items, IUser user)
        {
            if (items == null || items.Any()== false)
            {
                return new Result(false, "没有数据可以导入!");
            }

            await DB.FSql.Insert(items).ExecuteSqlBulkCopyAsync();

            return new Result(true);
        }


        public async Task<PageQueryResult<object>> Query(DateTime begin, DateTime end, string find, int pageNum, int pageSize)
        {
            begin = begin.Date;
            end = end.LastTimeOfDay();

            var temp = await DB.FSql.Select<LogisticsBillItem>()
                .Where(a=> a.FMonth>= begin && a.FMonth <= end)  
                .WhereIf(!string.IsNullOrEmpty(find), a => a.LogisticCode.Contains(find) || a.ShopName.Contains(find) || a.LogisticsCompany.Contains(find) || a.LogiAcct.Contains(find))
                .Page(pageNum, pageSize)
                .Count(out var c)
                .ToListAsync();

            var res = temp
                .Select(a => new
                {
                    id = a.id,
                    账单月份 = a.FMonth,
                    类别 = a.BillFlag,
                    账单日期 = a.DeliveryTime,
                    物流单号 = a.LogisticCode,
                    目的地省份 = a.ReceiveProvince,
                    目的地城市 = a.ReceiveCity,

                    揽收重量 = a.DeliveryWeight,
                    结算重量 = a.BillWeight,
                    运费 = a.Freight,
                    //店铺名称 = a.ShopName,
                    加收1= a.AddFee1,
                    加收2= a.AddFee2,
                    加收3= a.AddFee3,
                    加收4 = a.AddFee4,
                    总费用 = a.TotalFee,
                    预付单号钱= a.PrevPay,
                    需结算运费= a.Payable,
                    快递老板 = a.LogiAcct,
                    快递公司 = a.LogisticsCompany,

                })
                .ToList();

            return new PageQueryResult<object>(true, null, res) { Rows= c };
        }

        public async Task<Result> Delete(IEnumerable<int> items)
        {
            if (items == null || items.Any() == false)
            {
                return new Result(true);
            }

            await DB.FSql.Delete<LogisticsBillItem>()
                .Where(a => items.Contains(a.id))
                .ExecuteAffrowsAsync();

            return new Result(true);
        }




        public async Task<Result> DeleteItems(DateTime begin, DateTime end)
        {
            begin = begin.Date;
            end = end.LastTimeOfDay();

            await DB.FSql.Delete<LogisticsBillItem>()
                .Where(a => a.FMonth >= begin && a.FMonth <= end)
                .ExecuteAffrowsAsync();

            return new Result(true);    
        }



        public async Task<Result<IEnumerable<dynamic>>> QueryFeeReport(DateTime begin, DateTime end)
        {
            //var list = await DB.FSql.Select<LogisticsBillItem, LogiInfo>()
            //    .LeftJoin((a, b) => a.LogisticsCompany == b.logistics_name && b.isdel == false)
            //    .Where((a, b) => a.DeliveryTime >= begin && a.DeliveryTime <= end && a.BillFlag== "物流费用")
            //    .GroupBy((a, b) => new { b.LogiAcct })
            //    .ToListAsync(a => new
            //    {
            //        快递老板 =a.Key.LogiAcct,
            //        单数 = a.Count(),
            //        费用 = a.Sum(a.Value.Item1.TotalFee),
            //    });

            begin = begin.Date;
            end = end.LastTimeOfDay();


            var list = await DB.FSql.Select<LogisticsBillItem, LogiInfo>()
                .LeftJoin((a, b) => a.LogisticsCompany == b.logistics_name && b.isdel == false)
                .Where((a, b) => a.DeliveryTime >= begin && a.DeliveryTime <= end && a.BillFlag == "物流费用")
                .ToListAsync((a,b) => new
                {
                    快递老板 = a.LogiAcct,
                    单号= a.LogisticCode,
                    费用明细= a.Remarks,
                    费用 = a.TotalFee,
                });

            return new Result<IEnumerable<dynamic>>(list.Any(), null, list);
        }


        public async Task<Result<IEnumerable<dynamic>>> QueryBillReport(DateTime begin, DateTime end)
        {
            begin = begin.Date;
            end = end.LastTimeOfDay();

            //     var list = await DB.FSql.Select<LogisticsBillItem, LogiInfo, OutStockItem>()
            //.LeftJoin((a, b, c) => a.LogisticsCompany == b.logistics_name && b.isdel == false)
            //.LeftJoin((a, b, c) => a.LogisticCode == c.logistics_no)
            //.Where((a, b, c) => a.DeliveryTime >= begin && a.DeliveryTime <= end && a.BillFlag == "快递账单")
            //.GroupBy((a, b, c) => new { b.LogiAcct, la = a.LogiAcct, c.logistics_name })
            //.ToListAsync(a => new
            //{
            //    快递老板 = a.Key.LogiAcct,

            //    总计单数 = a.Count(),
            //    总计运费 = a.Sum(a.Value.Item1.TotalFee),
            //    总计结算重量 = a.Sum(a.Value.Item1.BillWeight),
            //});

            //var list = await DB.FSql.Select<LogisticsBillItem, OutStockItem, LogiInfo>()
            //    //.LeftJoin((a, b,c) => a.LogisticsCompany == b.logistics_name && b.isdel == false)
            //    .LeftJoin((a,b,c) => a.LogisticCode== b.logistics_no && string.IsNullOrEmpty(a.LogiAcct))
            //    .LeftJoin((a,b,c)=> b.logistics_name== c.logistics_name && c.isdel== false)
            //    .Where((a, b, c) => a.DeliveryTime >= begin && a.DeliveryTime <= end && a.BillFlag == "快递账单")
            //    .GroupBy((a, b, c) => new { c.LogiAcct, la = a.LogiAcct, })
            //    .ToListAsync(a => new
            //    {
            //        快递老板 = a.Key.LogiAcct,
            //        a.Key.la,
            //        总计单数= a.Count(),
            //        总计运费 = a.Sum(a.Value.Item1.TotalFee),
            //        总计结算重量 = a.Sum(a.Value.Item1.BillWeight),
            //    });

            var list = await DB.FSql.Select<LogisticsBillItem >()
            .Where(a => a.FMonth >= begin && a.FMonth <= end && a.BillFlag == "快递账单")
            .GroupBy(a => new { a.LogiAcct, })
            .ToListAsync(a => new
            {
                快递老板 = a.Key.LogiAcct,
                总计单数 = a.Count(),
                总计运费 = a.Sum(a.Value.TotalFee),
                总计结算重量 = a.Sum(a.Value.BillWeight),
            });

            var res = list.Select(a=> new
            {
                a.快递老板,
                a.总计单数,
                a.总计运费,
                a.总计结算重量,
            })
                .GroupBy(a=> a.快递老板)
                .Select(a=> new
                {
                    快递老板 = a.Key,
                    总计单数= a.Sum(c=> c.总计单数),
                    总计运费= a.Sum(c=> c.总计运费),
                    总计结算重量= a.Sum(c=> c.总计结算重量),
                })
                .ToList(); 

            return new Result<IEnumerable<dynamic>>(res.Any() , null, res);

        }

        /// <summary> 查询异常重复的账单 </summary>
        public async Task<IEnumerable<dynamic>> QueryRepeat(DateTime begin, DateTime end)
        {
            begin = begin.Date;
            end = end.LastTimeOfDay();

            var list = (await DB.FSql.Select<LogisticsBillItem>()
                .Where(a => a.BillFlag == "快递账单" && a.FMonth >= begin && a.FMonth <= end)
                .GroupBy(a => a.LogisticCode)
                .Having(a => a.Count() > 1)
                .WithTempQuery(g => new { LogisticCode = g.Key })
                .From<LogisticsBillItem>()
                .InnerJoin((a, b) => a.LogisticCode == b.LogisticCode)
                .ToListAsync((a, b) => new
                {
                    选择= false,
                    b.id,

                    账单月份 = b.FMonth,
                    类别 = b.BillFlag,
                    账单日期 = b.DeliveryTime,
                    物流单号 = b.LogisticCode,
                    目的地省份 = b.ReceiveProvince,
                    目的地城市 = b.ReceiveCity,

                    揽收重量 = b.DeliveryWeight,
                    结算重量 = b.BillWeight,
                    运费 = b.Freight,
                    //店铺名称 = b.ShopName,
                    加收1 = b.AddFee1,
                    加收2 = b.AddFee2,
                    加收3 = b.AddFee3,
                    加收4 = b.AddFee4,
                    总费用 = b.TotalFee,
                    预付单号钱 = b.PrevPay,
                    需结算运费 = b.Payable,
                    快递老板 = b.LogiAcct,
                    快递公司 = b.LogisticsCompany,

                }))
                .OrderBy(a=> a.物流单号)
                .ToList();

            return list;

            //var res = await DB.FSql.Select<LogisticsBillItem>()
            //    .Where(a=> a.BillFlag== "快递账单" && a.FMonth>= begin && a.FMonth<= end )
            //    .GroupBy(a=> a.LogisticCode)
        }
    }
}
